﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
   public class CollectionDAL
    {
        /// <summary>
        /// 收藏表
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        //商品收藏
        public static int collectionSC(Collection c)
        {
            string sql = "insert into tb_Collection(FoodID,UserID,Time) values(@FoodID,@UserID,@Time);";
            SqlParameter[] para = { 
            new SqlParameter("@FoodID",c.FoodID),
            new SqlParameter("@UserID",c.UserID),
            new SqlParameter("@Time",c.Time),
            };
            return SqlDBHelper.ExecuteNonQuery(sql, para);
        }
        //商品收藏取消
        public static int collectionQX(int id) {
            string sql = "delete from tb_Collection where FoodID="+id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //收藏刷新
        public static DataTable collectionSX() {
            string sql = "select c.CollectionID,f.FoodName,u.UserName,c.Time from tb_Collection c,tb_Food f,tb_User u where c.FoodID=f.FoodID and c.UserID=u.UserID";
            return SqlDBHelper.GetDataTable(sql);
        }
        //菜品名称查询
        public static DataTable cpmc(string name)
        {
            string sql = "select c.CollectionID,f.FoodName,u.UserName,c.Time from tb_Collection c,tb_Food f,tb_User u where c.FoodID=f.FoodID and c.UserID=u.UserID and f.FoodName like '%" + name + "%'";
            return SqlDBHelper.GetDataTable(sql);
        }
        //用户名称查询
        public static DataTable yhmc(int id) {
            string sql = "select c.CollectionID,f.FoodName,u.UserName,c.Time from tb_Collection c,tb_Food f,tb_User u where c.FoodID=f.FoodID and c.UserID=u.UserID and  u.UserID=" + id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //用户菜品名称查询
        public static DataTable yhcp(int id, string name)
        {
            string sql = "select c.CollectionID,f.FoodName,u.UserName,c.Time from tb_Collection c,tb_Food f,tb_User u where c.FoodID=f.FoodID and c.UserID=u.UserID and f.FoodName like '%" + name + "%' and  u.UserID=" + id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //根据id查询
        public static DataTable collectionID(int id)
        {
            string sql = "select c.CollectionID,f.FoodName,u.UserName,c.Time from tb_Collection c,tb_Food f,tb_User u where c.FoodID=f.FoodID and c.UserID=u.UserID and c.CollectionID="+id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台id查询
        public static DataTable phSCID(int id)
        {
            string sql = @"select c.CollectionID,f.FoodName,f.PicUrl,f.Price,c.Time from tb_Collection c,tb_Food f,tb_User u 
                          where c.FoodID=f.FoodID and c.UserID=u.UserID and u.UserID=" + id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台商品收藏取消
        public static int qhQX(int id)
        {
            string sql = "delete from tb_Collection where CollectionID="+ id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
    }
}
